/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package bd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import util.MySqlConfig;

/**
 * Fornece uma interface para execução de querys entre a aplicação e o SGBD
 * @author Gabriel
 *
 */
public class MySql {
    private MySqlConfig config;
    private Connection conn;

    /**
     * Data types suported by the getTable() method.
     * @author Gabriel Gravena Neves
     */
    public enum types{
        STRING,
        DATE,
        DOUBLE,
        INT
    }

    /**
     * Interface between the application and the MySql
     * @param host  Host
     * @param database Database
     * @param user User name
     * @param passwd Password
     */
    public MySql(String host, String database, String user, String passwd) {
        this.config = new MySqlConfig(host, database, user, passwd);
    }

    public MySql(MySqlConfig config) {
        this.config = config;
    }

    /**
     * Set a connection with the MySql database
     * @return True if the connection was successfull, false otherwise
     */
    private boolean setConnection() {
        String str_conn = "jdbc:mysql://" + this.config.getHost() + "/" + this.config.getDatabase();
        try {
            this.conn = DriverManager.getConnection(str_conn, this.config.getUser(), this.config.getPsswd());
            return true;
        } catch (SQLException ex) {
            System.out.println("Erro 0");
            return false;
        }
    }

    /**
     * Close the current connection with the MySql
     * @return True if the connection was closed successfully, false otherwise
     */
    private boolean closeConnection() {
        try {
            this.conn.close();
            return true;
        } catch (SQLException ex) {
            return false;
        }
    }

    /**
     * Return a ArrayList with the results of the SQL statement. Each element of
     * the ArrayList is one line of the result set. Each one of this lines contains
     * another ArrayList that stores the values of the colums.
     * @return Table of results
     * @param query The SQL statement to be executed
     * @param Mask[] A mask containing the data types to be returned by the data base
     * @see MySql.types
     */
    public ArrayList<ArrayList<String>> getTable(String query, types Mask[]) {
        try {
            Class.forName(this.config.getDriver());

            if(this.setConnection()) { // Tenta estabelecer conexão com o BD
                try {
                    /* Cria um statement para podermos mandar um SQL para o banco */
                    Statement stmt = this.conn.createStatement();
                    
                    ResultSet rs = stmt.executeQuery(query);

                    /* Linhas do resultado */
                    ArrayList<ArrayList<String>> temp = new ArrayList<ArrayList<String>>();


                    while(rs.next()) {
                        ArrayList<String> r = new ArrayList<String>(Mask.length);
                        for(int i=0;i<Mask.length;i++) {
                            if(Mask[i] == types.STRING) {
                                r.add(rs.getString(i+1));
                            } else if(Mask[i] == types.DATE) {
                                r.add(rs.getDate(i+1).toString());
                            } else if(Mask[i] == types.DOUBLE) {
                                r.add(Double.toString(rs.getDouble(i+1)));
                            } else if(Mask[i] == types.INT) {                                
                                //r.add(Integer.toString(rs.getInt(i+1)));
                                r.add(rs.getString(i+1));
                            }
                        }
                        temp.add(r);
                    }

                    rs.close();
                    stmt.close();

                    if(!this.closeConnection()) {
                        System.out.println("Erro 1");
                        return null; // Erro no fechamento da conexão
                    }
                    return temp;
                } catch (SQLException ex) {
                    //Tratar erro
                    System.out.println("Erro 2");
                    System.out.println(ex.getMessage());
                    return null;
                }
            } else {
                //Deu errado
                System.out.println("Erro 3");
                return null;
            }
        } catch (ClassNotFoundException ex) {
            //Erro interno do programa
            System.out.println("Erro 4");
            return null;
        }
    }

    /**
     * Return a ArrayList with the results of the SQL statement. Each element of
     * the ArrayList is one line of the result set. Each one of this lines contains
     * another ArrayList that stores the values of the colums.
     * @return Table of results
     * @param query The SQL statement to be executed
     */
    public ArrayList<ArrayList<String>> getTable(String query) {
        try {
            Class.forName(this.config.getDriver());

            if(this.setConnection()) { // Tenta estabelecer conexão com o BD
                try {
                    /* Cria um statement para podermos mandar um SQL para o banco */
                    Statement stmt = this.conn.createStatement();

                    ResultSet rs = stmt.executeQuery(query);

                    /* Linhas do resultado */
                    ArrayList<ArrayList<String>> temp = new ArrayList<ArrayList<String>>();


                    while(rs.next()) {
                        /* Colunas do resultado */
                        ArrayList<String> r = new ArrayList<String>(rs.getMetaData().getColumnCount());
                        for(int i=0;i<rs.getMetaData().getColumnCount();i++) {
                            r.add(rs.getString(i+1));

                        }
                        temp.add(r);
                    }

                    rs.close();
                    stmt.close();

                    if(!this.closeConnection()) {
                        System.out.println("Erro 1");
                        return null; // Erro no fechamento da conexão
                    }
                    return temp;
                } catch (SQLException ex) {
                    //Tratar erro
                    System.out.println("Erro 2");
                    System.out.println(ex.getMessage());
                    return null;
                }
            } else {
                //Deu errado
                System.out.println("Erro 3");
                return null;
            }
        } catch (ClassNotFoundException ex) {
            //Erro interno do programa
            System.out.println("Erro 4");
            return null;
        }
    }

    /**
     *
     * @param query A SQL query containing a INSERT, UPDATE or DELETE command
     * @return true if the query was executed successfully
     */
    public boolean insert(String query) {
        try {
            Class.forName(this.config.getDriver());

            if(this.setConnection()) {
                Statement stmt = this.conn.createStatement();
                int executeUpdate = stmt.executeUpdate(query);

                System.out.println("Resultado: " + executeUpdate);

                if(!this.closeConnection()) {
                    //Erro ao fechar conexão
                }
                return true;
            } else {
                return false;
            }
        } catch(ClassNotFoundException ex) {
            // Erro ao carregar driver
            System.out.println("Erro ao carregar driver");
        } catch(SQLException ex) {
            // Erro na sintaxe SQL
            System.out.println("Erro na sitaxe SQL");
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Erro no SQL", JOptionPane.ERROR_MESSAGE);
        }


        return true;
    }
}
